package com.GroupSix.tool;
import com.GroupSix.entity.QuestionBankEntity;
import com.GroupSix.service.QuestionBankService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.time.LocalDateTime;
import java.util.HashSet;
import java.util.Set;


@Component
public class ExcelImporter {

    @Autowired
    private QuestionBankService questionBankService;

    // 存储已导入的题目内容，用于去重
    private Set<String> importedQuestions = new HashSet<>();

    public int importExcel(String filePath) throws IOException {
        int importedCount = 0;
        try (FileInputStream fis = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;
                QuestionBankEntity question = createQuestionFromRow(row);
                if (question!= null && isValid(question)) {
                    if (isDuplicate(question)) {
                        continue;
                    }
                    questionBankService.add(question);
                    importedCount++;
                    importedQuestions.add(question.getQscontent());
                }
            }
        } catch (IOException e) {
            // 更详细的异常处理
            System.err.println("Error importing Excel file: " + e.getMessage());
            throw e;
        }
        return importedCount;
    }

    private QuestionBankEntity createQuestionFromRow(Row row) {
        DataFormatter formatter = new DataFormatter();
        QuestionBankEntity question = new QuestionBankEntity();
        question.setQscontent(getCellValueAsString(row.getCell(1), formatter));
        question.setQsscore((int) getCellValueAsDouble(row.getCell(2), formatter));
        question.setQsanswer(getCellValueAsString(row.getCell(3), formatter));
        question.setQsdifficulty((int) getCellValueAsDouble(row.getCell(4), formatter));
        question.setQsknowledge((int) getCellValueAsDouble(row.getCell(5), formatter));
        question.setQstype((int) getCellValueAsDouble(row.getCell(6), formatter));
        question.setQscourse((int) getCellValueAsDouble(row.getCell(7), formatter));
        question.setQsA(getCellValueAsString(row.getCell(8), formatter));
        question.setQsB(getCellValueAsString(row.getCell(9), formatter));
        question.setQsC(getCellValueAsString(row.getCell(10), formatter));
        question.setQsD(getCellValueAsString(row.getCell(11), formatter));
        question.setQsE(getCellValueAsString(row.getCell(12), formatter));
        question.setQsisActive(0);
        question.setQscreateTime(LocalDateTime.now());
        return question;
    }

    private boolean isValid(QuestionBankEntity question) {
        // 检查必填字段是否为空
        return question.getQscontent()!= null &&!question.getQscontent().isEmpty();
    }

    private String getCellValueAsString(Cell cell, DataFormatter formatter) {
        return (cell == null)? null : formatter.formatCellValue(cell);
    }

    private double getCellValueAsDouble(Cell cell, DataFormatter formatter) {
        String value = getCellValueAsString(cell, formatter);
        try {
            return (value == null)? 0 : Double.parseDouble(value);
        } catch (NumberFormatException e) {
            return 0;
        }
    }
    private boolean isDuplicate(QuestionBankEntity question) {
        // 同时检查当前导入的集合和数据库中是否存在相同的题目内容
        return importedQuestions.contains(question.getQscontent()) || questionBankService.existsByContent(question.getQscontent());
    }
}